/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Category;
import model.Product;
import model.ProductSearch;

/**
 *
 * @author Thai Ha Cong
 */
public class ProductDAO {

    private Connection con;

    public ProductDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }

    public Product getProductFromProductName(String pName) {
        Product result = new Product();

        try {
            String sqlst = "SELECT * FROM product WHERE name = ?";

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, pName);

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                Integer idCategory = rs.getInt("Category_idCategory");
                Category category = this.getCategoryFromId(idCategory);

                String dimension = rs.getString("dimension");
                String weight = rs.getString("weight");
                Integer warranty = rs.getInt("warranty");
                String color = rs.getString("color");
                Double screenSize = rs.getDouble("screenSize");
                String display = rs.getString("display");
                Integer storage = rs.getInt("storage");
                Double camera = rs.getDouble("camera");
                String battery = rs.getString("battery");
                String os = rs.getString("os");
                String data = rs.getString("data");
                String processor = rs.getString("processor");
                String memory = rs.getString("memory");
                String graphics = rs.getString("graphics");
                String hardDrive = rs.getString("hardDrive");
                String packageContent = rs.getString("packageContent");

                result = new Product(idProduct, code, name, summary, details, brand, others, unitPrice, stockQuantity, imageurl, category, dimension, weight, warranty, color, screenSize, display, storage, camera, battery, os, data, processor, memory, graphics, hardDrive, packageContent);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public Product getProductFromProductId(int pid) {
        Product result = new Product();

        try {
            String sqlst = "SELECT * FROM product WHERE idProduct = ?";

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, pid);

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                Integer idCategory = rs.getInt("Category_idCategory");
                Category category = this.getCategoryFromId(idCategory);

                String dimension = rs.getString("dimension");
                String weight = rs.getString("weight");
                Integer warranty = rs.getInt("warranty");
                String color = rs.getString("color");
                Double screenSize = rs.getDouble("screenSize");
                String display = rs.getString("display");
                Integer storage = rs.getInt("storage");
                Double camera = rs.getDouble("camera");
                String battery = rs.getString("battery");
                String os = rs.getString("os");
                String data = rs.getString("data");
                String processor = rs.getString("processor");
                String memory = rs.getString("memory");
                String graphics = rs.getString("graphics");
                String hardDrive = rs.getString("hardDrive");
                String packageContent = rs.getString("packageContent");

                result = new Product(idProduct, code, name, summary, details, brand, others, unitPrice, stockQuantity, imageurl, category, dimension, weight, warranty, color, screenSize, display, storage, camera, battery, os, data, processor, memory, graphics, hardDrive, packageContent);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    
     public Product getProductShortFromProductId(int pid) {
        Product result = new Product();

        try {
            String sqlst = "SELECT * FROM product WHERE idProduct = ?";

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, pid);

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                

                result = new Product( idProduct,  code,  name,  brand,  unitPrice);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public Category getCategoryFromId(int id) {
        Category result = new Category();

        try {
            String sqlst = "SELECT * FROM category WHERE idCategory = ?";

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, id);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                Integer idCategory = rs.getInt("idCategory");
                String name = rs.getString("name");
                String description = rs.getString("description");

                result = new Category(idCategory, name, description);

                Category parentCategory;
                int parentid = rs.getInt("parentid");
                if (parentid != 0) {
                    String sqlst2 = "SELECT * FROM category WHERE parentid = ?";

                    PreparedStatement ps2 = con.prepareStatement(sqlst2);
                    ps2.setInt(1, parentid);

                    ResultSet rs2 = ps2.executeQuery();
                    while (rs2.next()) {
                        Integer cidCategory = rs.getInt("idCategory");
                        String cname = rs.getString("name");
                        String cdescription = rs.getString("description");

                        parentCategory = new Category(cidCategory, cname, cdescription);

                        result.setParentCategory(parentCategory);
                    }
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<String> getAllBrands() {
        List<String> result = new ArrayList<String>();

        try {
            String sqlst = "SELECT DISTINCT product.brand FROM onlineshopping.product "
                    + "ORDER by product.brand;";
            PreparedStatement ps = con.prepareStatement(sqlst);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                String s = rs.getString(1);
                result.add(s);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<Category> getAllCategories() {
        List<Category> result = new ArrayList<Category>();

        try {
            //get parent categories
            String sqlst = "SELECT * FROM category WHERE parentid IS NULL";
            PreparedStatement ps = con.prepareStatement(sqlst);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idCategory = rs.getInt("idCategory");
                String name = rs.getString("name");
                String description = rs.getString("description");

                Category temp = new Category(idCategory, name, description);

                //get children of each parent
                List<Category> childCategories = new ArrayList<Category>();

                String sqlst2 = "SELECT * FROM category WHERE parentid = ?";
                PreparedStatement ps2 = con.prepareStatement(sqlst2);
                ps2.setInt(1, idCategory);

                ResultSet rs2 = ps2.executeQuery();

                while (rs2.next()) {
                    Integer idCategory2 = rs2.getInt("idCategory");
                    String name2 = rs2.getString("name");
                    String description2 = rs2.getString("description");

                    Category childTemp = new Category(idCategory2, name2, description2);
                    childTemp.setParentCategory(temp);

                    childCategories.add(childTemp);
                }

                //set children
                temp.setChildCategories(childCategories);

                //add to all
                result.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<Category> getAllEqualCategories() {
        List<Category> result = new ArrayList<Category>();

        try {
            //get parent categories
            String sqlst = "SELECT * FROM category";
            PreparedStatement ps = con.prepareStatement(sqlst);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idCategory = rs.getInt("idCategory");
                String name = rs.getString("name");
                String description = rs.getString("description");

                Category temp = new Category(idCategory, name, description);

                result.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public boolean addProduct(Product product) {
        boolean result = false;

        try {
            String sqlst = "INSERT INTO product(code, name, summary, details, brand, others, "
                    + "unitPrice, stockQuantity, sale, imageurl, Category_idCategory) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, product.getCode());
            ps.setString(2, product.getName());
            ps.setString(3, product.getSummary());
            ps.setString(4, product.getDetails());
            ps.setString(5, product.getBrand());
            ps.setString(6, product.getOthers());
            ps.setDouble(7, product.getUnitPrice());
            ps.setInt(8, product.getStockQuantity());
            ps.setDouble(9, product.getSale());
            ps.setString(10, product.getImageurl());
            ps.setInt(11, product.getCategory().getIdCategory());

            ps.executeUpdate();

            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public boolean addProductInfor(Product product) {
        boolean result = false;

        try {
            String sqlst = "INSERT INTO product(code, name, summary, details, brand, others, "
                    + "unitPrice, stockQuantiy, sale, imageurl, Category_idCategory) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, product.getCode());
            ps.setString(1, product.getName());
            ps.setString(2, product.getSummary());
            ps.setString(3, product.getDetails());
            ps.setString(4, product.getBrand());
            ps.setString(5, product.getOthers());
            ps.setDouble(6, product.getUnitPrice());
            ps.setInt(7, product.getStockQuantity());
            ps.setDouble(8, product.getSale());
            ps.setString(9, product.getImageurl());
            ps.setInt(10, product.getCategory().getIdCategory());
            System.out.println("Pass2");
            ps.executeUpdate();

            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public boolean addCategory(Category category) {
        boolean result = false;

        try {
            String sqlst;
            PreparedStatement ps;

            if (category.getParentCategory() == null) {
                sqlst = "INSERT INTO category(name, description) VALUES (?, ?)";
                ps = con.prepareStatement(sqlst);
                ps.setString(1, category.getName());
                ps.setString(2, category.getDescription());
            } else {
                sqlst = "INSERT INTO category(name, description, parentid) VALUES (?, ?, ?)";
                ps = con.prepareStatement(sqlst);
                ps.setString(1, category.getName());
                ps.setString(2, category.getDescription());
                ps.setInt(3, category.getParentCategory().getIdCategory());
            }

            ps.executeUpdate();
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public int countProductsFromCategoryID(int idCategory) {
        int result = 0;

        try {
            String sqlst = "SELECT COUNT(product.idProduct) FROM product, category"
                    + " WHERE (product.Category_idCategory = ?"
                    + " OR category.parentid = ?)"
                    + " AND product.Category_idCategory = category.idCategory";

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, idCategory);
            ps.setInt(2, idCategory);

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                result = rs.getInt(1);
            }
            int tt = 9;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<Product> getProductsFromCategoryID(int idCategory, int offset, int length) {
        List<Product> result = new ArrayList<Product>();

        try {
            String sqlst = "SELECT product.* FROM product, category"
                    + " WHERE (product.Category_idCategory = ?"
                    + " OR category.parentid = ?)"
                    + " AND product.Category_idCategory = category.idCategory"
                    + " LIMIT ?, ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, idCategory);
            ps.setInt(2, idCategory);
            ps.setInt(3, offset);
            ps.setInt(4, length);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                result.add(new Product(idProduct, code, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<Product> getProductsFromCategoryID(int idCategory) {
        List<Product> result = new ArrayList<Product>();

        try {
            String sqlst = "SELECT product.* FROM product, category"
                    + " WHERE (product.Category_idCategory = ?"
                    + " OR category.parentid = ?)"
                    + " AND product.Category_idCategory = category.idCategory";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, idCategory);
            ps.setInt(2, idCategory);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                result.add(new Product(idProduct, code, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<Product> getSaleProducts() {
        List<Product> result = new ArrayList<Product>();

        try {
            String sqlst = "SELECT * FROM product ORDER BY sale DESC LIMIT 12";
            PreparedStatement ps = con.prepareStatement(sqlst);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                result.add(new Product(idProduct, code, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<Product> getNewestProducts() {
        List<Product> result = new ArrayList<Product>();

        try {
            String sqlst = "SELECT * FROM product ORDER BY idProduct DESC LIMIT 12";
            PreparedStatement ps = con.prepareStatement(sqlst);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                result.add(new Product(idProduct, code, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<Product> getBestSellingProducts() {
        List<Product> result = new ArrayList<Product>();

        try {
            String sqlst = "SELECT product.*, SUM(orderitem.orderQuantity) AS totalQuantity"
                    + " FROM product, orderitem"
                    + " WHERE product.idProduct = orderitem.Product_idProduct"
                    + " GROUP BY product.idProduct ORDER BY totalQuantity DESC LIMIT 12";
            PreparedStatement ps = con.prepareStatement(sqlst);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                result.add(new Product(idProduct, code, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<Product> searchForProductsByModel(ProductSearch productSearch) {
        List<Product> result = new ArrayList<Product>();

        try {
            String sqlst = "SELECT product.* FROM product, category "
                    + "WHERE product.name LIKE ? "
                    + "AND (product.unitPrice - product.sale) >= ? "
                    + "AND (product.unitPrice - product.sale) <= ? "
                    + "AND product.brand LIKE ? "
                    + "AND product.Category_idCategory = category.idCategory ";

            Integer categoryid = productSearch.getCategoryid();
            if (categoryid > 0) {
                sqlst = sqlst + "AND (category.parentid = '" + categoryid + "' "
                        + "OR category.idCategory = '" + categoryid + "') ";
            }

            String warranty = productSearch.getWarranty();
            if (warranty.equals("0 - 12 months")) {
                sqlst = sqlst + "AND product.warranty <= 12 ";
            } else if (warranty.equals("13 - 36 months")) {
                sqlst = sqlst + "AND product.warranty > 12 AND product.warranty <= 36 ";
            } else if (warranty.equals("> 36 months")) {
                sqlst = sqlst + "AND product.warranty > 36 ";
            }

            String screen = productSearch.getScreen();
            if (screen.equals("<= 4.0 inches")) {
                sqlst = sqlst + "AND product.screenSize <= 4.0 ";
            } else if (screen.equals("4.1 - 6.5 inches")) {
                sqlst = sqlst + "AND product.screenSize >= 4.1 AND product.screenSize <= 6.5 ";
            } else if (screen.equals("6.6 - 12.5 inches")) {
                sqlst = sqlst + "AND product.screenSize >= 6.6 AND product.screenSize <= 12.5 ";
            } else if (screen.equals("12.6 - 14.0 inches")) {
                sqlst = sqlst + "AND product.screenSize >= 12.6 AND product.screenSize <= 14.0 ";
            } else if (screen.equals("> 14.0 inches")) {
                sqlst = sqlst + "AND product.screenSize > 14.0 ";
            }

            String storage = productSearch.getStorage();
            if (storage.equals("<= 16GB")) {
                sqlst = sqlst + "AND product.storage <= 16 ";
            } else if (storage.equals("17 - 32GB")) {
                sqlst = sqlst + "AND product.storage >= 17 AND product.storage <= 32 ";
            } else if (storage.equals("33 - 64GB")) {
                sqlst = sqlst + "AND product.storage >= 33  AND product.storage <= 64 ";
            } else if (storage.equals("65 - 256GB")) {
                sqlst = sqlst + "AND product.storage >= 65  AND product.storage <= 256 ";
            } else if (storage.equals("257 - 750GB")) {
                sqlst = sqlst + "AND product.storage >=257  AND product.storage <= 750 ";
            } else if (storage.equals("> 750GB")) {
                sqlst = sqlst + "AND product.storage > 750 ";
            }

            String camera = productSearch.getCamera();
            if (camera.equals("<= 3.2MP")) {
                sqlst = sqlst + "AND product.camera <= 16 ";
            } else if (camera.equals("3.3 - 8.0MP")) {
                sqlst = sqlst + "AND product.camera >= 3.3 AND product.camera <= 8.0 ";
            } else if (camera.equals("8.1 - 16.0MP")) {
                sqlst = sqlst + "AND product.camera >= 8.1 AND product.camera <= 16.0 ";
            } else if (camera.equals("> 16MP")) {
                sqlst = sqlst + "AND product.camera > 16.0 ";
            }

            String sbrand = productSearch.getBrand();
            if (sbrand.equals("All")) {
                sbrand = "";
            }
            
            String scolor = productSearch.getColor();
            if (!scolor.equals("")){
                sqlst = sqlst + "AND product.color LIKE ? ";
            }
            
            String sos = productSearch.getOs();
            if (!sos.equals("All")) {
                sqlst = sqlst + "AND product.os LIKE ? ";
            }
            
            String sort = productSearch.getSort();

            if (sort.equals("Product Name")) {
                sqlst = sqlst + "ORDER BY product.name";
            } else if (sort.equals("Ascending Price")) {
                sqlst = sqlst + "ORDER BY (product.unitPrice - product.sale) ASC";
            } else if (sort.equals("Descending Price")) {
                sqlst = sqlst + "ORDER BY (product.unitPrice - product.sale) DESC";
            } else if (sort.equals("Product Brand")) {
                sqlst = sqlst + "ORDER BY product.brand";
            }

            sqlst = sqlst + " LIMIT 12";
            
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, "%" + productSearch.getProductName() + "%");
            ps.setDouble(2, productSearch.getMinPrice());
            ps.setDouble(3, productSearch.getMaxPrice());
            ps.setString(4, "%" + sbrand + "%");
            
            if (!scolor.equals("")){
                ps.setString(5, "%" + scolor + "%");
            }
            if (!sos.equals("All")) {
                ps.setString(6, "%" + sos+ "%");
            }

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                result.add(new Product(idProduct, code, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("\n---" + result);
        return result;
    }

    public List<Product> searchForProductsByKeyword(String keyword) {
        List<Product> result = new ArrayList<Product>();

        try {
            String sqlst = "SELECT * FROM product"
                    + " WHERE name LIKE ?"
                    + " LIMIT 36";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, "%" + keyword + "%");

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                result.add(new Product(idProduct, code, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<Product> getRecommendedProducts(Integer pid) {
        List<Product> result = new ArrayList<Product>();

        try {
            String sqlst = "SELECT p.* , i2.Product_idProduct, SUM(i2.orderQuantity) AS tquantity, COUNT(i2.orderQuantity) AS tcount "
                    + "FROM orderitem i1, orderitem i2, product p "
                    + "WHERE i2.Order_idOrder = i1.Order_idOrder "
                    + "	AND i1.Product_idProduct = ? "
                    + "	AND i2.Product_idProduct != i1.Product_idProduct "
                    + "	AND p.idProduct = i2.Product_idProduct "
                    + "GROUP BY i2.Product_idProduct "
                    + "ORDER BY tcount DESC, (tquantity) DESC "
                    + "LIMIT 4";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, pid);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt("idProduct");
                String code = rs.getString("code");
                String name = rs.getString("name");
                String summary = rs.getString("summary");
                String details = rs.getString("details");
                String brand = rs.getString("brand");
                String others = rs.getString("others");
                double unitPrice = rs.getDouble("unitPrice");
                int stockQuantity = rs.getInt("stockQuantity");
                double sale = rs.getDouble("sale");
                String imageurl = rs.getString("imageurl");

                result.add(new Product(idProduct, code, name, summary, details, brand, others, unitPrice, stockQuantity, sale, imageurl));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
}
